package com.uspacex.calculate.mapper;

import com.uspacex.calculate.entity.MemberTrace;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


@Mapper
public interface DisplayMapper {


    @Select("SELECT * FROM source.enterprise_list")
    List<LinkedHashMap<String, Object>> enterpriseList();


    //db_enterprise_info business_license
    @Select("SELECT " +
            "column_name, column_comment " +
            "FROM information_schema.columns " +
            "WHERE table_schema = #{dbName} AND table_name = #{tableName} AND column_comment != ''")
    List<LinkedHashMap<String, Object>> selectComment(@Param("dbName") String dbName, @Param("tableName") String tableName);


    @Select("SELECT * FROM db_enterprise_info.business_license WHERE enterprise_name = #{name}")
    List<LinkedHashMap<String, Object>> businessLicense(String name);


    @Select("SELECT * FROM db_enterprise_info.enterprise_public_opinion WHERE enterprise_name = #{name} ORDER BY publication_time DESC ")
    List<LinkedHashMap<String, Object>> enterprisePublicOpinion(String name);


    @Select("SELECT count(*) AS 'count', event_classification FROM db_enterprise_info.enterprise_public_opinion " +
            "WHERE enterprise_name = #{name} GROUP BY event_classification")
    List<LinkedHashMap<String, Object>> enterprisePublicOpinionCount(String name);


    @Select("SELECT enterprise_name, date_publication, judicial_type, title, content " +
            "FROM db_enterprise_info.adjudication_document " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT enterprise_name, date_publication, judicial_type, title, content " +
            "FROM db_enterprise_info.announcement_court " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT enterprise_name, date_publication, judicial_type, title, content " +
            "FROM db_enterprise_info.announcement_faithless " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT enterprise_name, date_publication, judicial_type, title, content " +
            "FROM db_enterprise_info.announcement_trial " +
            "WHERE enterprise_name = #{name} " +
            "ORDER BY date_publication DESC")
    List<LinkedHashMap<String, Object>> justiceInformation (String name);


    @Select("SELECT count(*) as 'count', '裁判文书' as 'judicial_type' " +
            "FROM db_enterprise_info.adjudication_document " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT count(*) as 'count', '法院公告' as 'judicial_type' " +
            "FROM db_enterprise_info.announcement_court " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT count(*) as 'count', '失信公告' as 'judicial_type' " +
            "FROM db_enterprise_info.announcement_faithless " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT count(*) as 'count', '开庭公告' as 'judicial_type' " +
            "FROM db_enterprise_info.announcement_trial " +
            "WHERE enterprise_name = #{name} " +
            "GROUP BY judicial_type" )
    List<LinkedHashMap<String, Object>> justiceInformationCount (String name);


    @Select("SELECT release_date as 'date', " +
            "CASE WHEN(patent_name != '') THEN patent_name " +
            "ELSE abstract END AS 'name', '专利' as 'type' " +
            "FROM db_enterprise_info.enterprise_patent " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT registration_approval_date as 'date', software_name AS 'name', '软著' as 'type' " +
            "FROM db_enterprise_info.enterprise_software_copyright " +
            "WHERE enterprise_name = #{name} " +
            "ORDER BY date DESC " )
    List<LinkedHashMap<String, Object>> intellectualProperty (String name);


    @Select("SELECT count(*) as 'count', '专利' as 'type' " +
            "FROM db_enterprise_info.enterprise_patent " +
            "WHERE enterprise_name = #{name} " +
            "UNION ALL " +
            "SELECT count(*) as 'count', '软著' as 'type' " +
            "FROM db_enterprise_info.enterprise_software_copyright " +
            "WHERE enterprise_name = #{name} " )
    List<LinkedHashMap<String, Object>> intellectualPropertyCount (String name);


    @Select("SELECT * FROM db_enterprise_info.enterprise_matter_alteration WHERE enterprise_name = #{name} " +
            "ORDER BY date DESC ")
    List<LinkedHashMap<String, Object>> alteration(String name);


    @Select("SELECT count(*) AS 'count', matter FROM db_enterprise_info.enterprise_matter_alteration " +
            "WHERE enterprise_name = #{name} " +
            "GROUP BY matter")
    List<LinkedHashMap<String, Object>> alterationCount(String name);


    @Select("SELECT * FROM db_enterprise_info.balance_sheet ORDER BY date_info ASC ")
    List<LinkedHashMap<String, Object>> balanceSheet(String name);


    @Select("SELECT * FROM db_enterprise_info.cash_flow_statement ORDER BY date_info ASC ")
    List<LinkedHashMap<String, Object>> cashFlow(String name);


    @Select("SELECT * FROM db_enterprise_info.profit_statement ORDER BY date_info ASC ")
    List<LinkedHashMap<String, Object>> profit(String name);


    @Select("SELECT * FROM source.enterprise_member_trace WHERE enterprise_name = #{name}")
    List<LinkedHashMap<String, Object>> memberTrace(String name);

    @Select("INSERT INTO source.enterprise_member_trace (enterprise_name, member_name,date_info," +
            "            longitude,latitude,times,position)VALUES (#{enterpriseName},#{memberName},#{dateInfo}," +
            "#{longitude},#{latitude},#{times},#{position})")
    List<LinkedHashMap<String, Object>> insertTrace(MemberTrace mt);

    @Select("SELECT * FROM db_enterprise_info.member WHERE enterprise_name = #{name}")
    List<LinkedHashMap<String, Object>> member(String name);

}
